﻿USE PhoneDb;

IF OBJECT_ID('Users') IS NULL
BEGIN
	USE PhoneDb;
	
	-- CREATE TABLES in PhoneDb

	CREATE TABLE dbo.Departments
	(
		DepartmentId	int				NOT NULL	IDENTITY (1, 1),
		Name			varchar (50)	NOT NULL,
		CONSTRAINT PK_Departments
			PRIMARY KEY (DepartmentId)
	);
	
	CREATE TABLE dbo.Users
	(
		UserId 			int 			NOT NULL 	IDENTITY (1, 1),
		Name 			varchar (50) 	NOT NULL,
		DepartmentId	int				NOT NULL,
		CONSTRAINT PK_Users
			PRIMARY KEY (UserId),
		CONSTRAINT FK_Departments
			FOREIGN KEY (DepartmentId)
			REFERENCES dbo.Departments (DepartmentId)
			ON DELETE CASCADE
	);
	
	CREATE TABLE dbo.Phones
	(
		UserId			int 			NOT NULL,
		Phone			varchar(12)		NOT NULL,
		CONSTRAINT PK_Phones
			PRIMARY KEY (Phone),
		CONSTRAINT CK_VictoriaPhoneNumber
			CHECK ( Phone LIKE '0[3-4] [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]' ),
		--CONSTRAINT CK_PhoneLength
		--	CHECK ( len(Phone) = 10),
		--CONSTRAINT CK_IsPhoneNumber
		--	CHECK ( ISNUMERIC(Phone) = 1 ),
		CONSTRAINT FK_Users
			FOREIGN KEY (UserId)
			REFERENCES dbo.Users (UserId)
			ON DELETE CASCADE
	);
	
	-- Insert Data

	INSERT INTO dbo.Departments (Name) VALUES ('Air');
	INSERT INTO dbo.Departments (Name) VALUES ('Earth');
	INSERT INTO dbo.Departments (Name) VALUES ('Element');
	INSERT INTO dbo.Departments (Name) VALUES ('Fire');
	INSERT INTO dbo.Departments (Name) VALUES ('Water');
	
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Aang', 	 1);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Appa', 	 1);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Gyatso', 1);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Momo',   1);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Toph',   2);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Bumi',   2);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Suki',   3);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Jet',    3);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Azula',  4);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Iroh',   4);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Zuko',   4);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Katara', 5);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Sokka',  5);
	INSERT INTO dbo.Users (Name, DepartmentId) VALUES ('Yue', 	 5);
	
	INSERT INTO dbo.Phones VALUES ( 1, '03 9111 1119');
	INSERT INTO dbo.Phones VALUES ( 1, '03 9111 1111');
	INSERT INTO dbo.Phones VALUES ( 1, '03 9111 1166');
	INSERT INTO dbo.Phones VALUES ( 1, '03 9222 2222');
	INSERT INTO dbo.Phones VALUES ( 1, '03 9333 3333');
	INSERT INTO dbo.Phones VALUES ( 2, '03 8111 1111');
	INSERT INTO dbo.Phones VALUES ( 2, '03 8111 1122');
	INSERT INTO dbo.Phones VALUES ( 3, '03 8222 2222');
	INSERT INTO dbo.Phones VALUES ( 3, '03 8222 2233');
	INSERT INTO dbo.Phones VALUES ( 4, '03 7333 3333');
	INSERT INTO dbo.Phones VALUES ( 5, '03 7111 1111');
	INSERT INTO dbo.Phones VALUES ( 6, '03 7222 2222');
	INSERT INTO dbo.Phones VALUES ( 7, '03 7611 1111');
	INSERT INTO dbo.Phones VALUES ( 8, '03 7622 2222');
	INSERT INTO dbo.Phones VALUES ( 9, '03 6111 1111');
	INSERT INTO dbo.Phones VALUES ( 9, '03 6111 1122');
	INSERT INTO dbo.Phones VALUES (10, '03 6222 2222');
	INSERT INTO dbo.Phones VALUES (10, '03 6222 2233');
	INSERT INTO dbo.Phones VALUES (11, '03 6333 3322');
	INSERT INTO dbo.Phones VALUES (11, '03 5333 3333');
	INSERT INTO dbo.Phones VALUES (12, '03 5211 1111');
	INSERT INTO dbo.Phones VALUES (12, '03 5211 1122');
	INSERT INTO dbo.Phones VALUES (13, '03 5222 2222');
	INSERT INTO dbo.Phones VALUES (13, '03 5222 2211');
	INSERT INTO dbo.Phones VALUES (14, '03 5233 3333');
	INSERT INTO dbo.Phones VALUES (14, '03 5233 3311');
	
						----------------
						-- Encryption --
						----------------
					
	--If there is no master key, create one now. 
	IF NOT EXISTS 
		(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
		CREATE MASTER KEY 
		ENCRYPTION BY PASSWORD = 'Pa$sword';
	
	-- Create server certificate
	CREATE CERTIFICATE PhoneCertificate
	   WITH SUBJECT = 'Encrypted Phone Numbers';
	
	CREATE SYMMETRIC KEY SSN_Key_01
		WITH ALGORITHM = AES_256
		ENCRYPTION BY CERTIFICATE PhoneCertificate;
	
	-- Create a column in which to store the encrypted data.
	ALTER TABLE dbo.Phones
		ADD EncryptedPhone varbinary(128); 
	
	-- Open the symmetric key with which to encrypt the data.
	OPEN SYMMETRIC 
		KEY SSN_Key_01 DECRYPTION 
		BY CERTIFICATE PhoneCertificate;

	-- Encrypt the value in column NationalIDNumber with symmetric 
	-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
	UPDATE dbo.Phones
		SET EncryptedPhone = EncryptByKey(Key_GUID('SSN_Key_01'), Phone);
	
	-- Verify the encryption.
	-- First, open the symmetric key with which to decrypt the data.
	OPEN SYMMETRIC 
		KEY SSN_Key_01 DECRYPTION 
		BY CERTIFICATE PhoneCertificate;
	
	-- Now list the original ID, the encrypted ID, and the 
	-- decrypted ciphertext. If the decryption worked, the original
	-- and the decrypted ID will match.
	SELECT Phone, EncryptedPhone
		AS 'Encrypted Phone Number',
		CONVERT(varchar, DecryptByKey(EncryptedPhone)) 
		AS 'Decrypted Phone Number'
		FROM dbo.Phones;
	
	-- Create View
	EXEC
	(
		'CREATE VIEW PhonesOwnedByUser
			AS
				SELECT Users.UserId, 
					   Users.Name 				AS Name, 
					   Departments.DepartmentId,
					   Departments.Name 		AS Department, 
					   Phone,
					   EncryptedPhone
				FROM   [PhoneDb].[dbo].[Users] 			AS Users
				JOIN   [PhoneDb].[dbo].[Departments]	AS Departments
					ON Users.DepartmentId = Departments.DepartmentId
				JOIN   [PhoneDb].[dbo].[Phones] 		AS Phones
					ON Users.UserId = Phones.UserId;'
	);
	

	-----------------------------------
	-- DEPARTMENTS' STORED PROCEDURE --
	-----------------------------------
	EXEC
	(
		'CREATE PROCEDURE GetAllDepartments
			AS
				SELECT * 
				FROM [PhoneDb].[dbo].[Departments];'
	);

	EXEC
	(
		'CREATE PROCEDURE GetDepartmentById
				@DepartmentId varchar(10)
			AS
			BEGIN
				SELECT *
				FROM [PhoneDb].[dbo].[Departments]
				WHERE Departments.DepartmentId = CAST(@DepartmentId as int);
			END;'
	);

	EXEC
	(
		'CREATE PROCEDURE InsertDepartment
				@DepartmentName varchar(50)
			AS
				INSERT INTO dbo.Departments (Name) 
				VALUES (@DepartmentName);'
	);

	------------------------------
	-- PHONES' STORED PROCEDURE --
	------------------------------
	EXEC
	(
		'CREATE PROCEDURE GetPlainPhone
				@PhoneNumber varchar(12)
			AS		
				SELECT *
				FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
				WHERE Phone = @PhoneNumber
				ORDER BY Department, UserId;'
	);

	EXEC
	(
		'CREATE PROCEDURE GetEncryptedPhone
				@PlainPhoneNumber varchar(12)
			AS
			BEGIN		
				OPEN SYMMETRIC KEY SSN_Key_01 
					DECRYPTION BY CERTIFICATE PhoneCertificate;
		
				SELECT * 
				FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
				WHERE CONVERT(varchar, DecryptByKey(EncryptedPhone)) = @PlainPhoneNumber
				ORDER BY Department, UserId;
		
				CLOSE SYMMETRIC KEY SSN_Key_01;
			END;'
	);

	EXEC
	(
		'CREATE PROCEDURE InsertNewPhone
				@UserId varchar(10),
				@Phone  varchar(12)
			AS
			BEGIN
				OPEN SYMMETRIC KEY SSN_Key_01 
					DECRYPTION BY CERTIFICATE PhoneCertificate;
			
				INSERT INTO [PhoneDb].[dbo].[Phones]
				VALUES ( @UserId, @Phone, EncryptByKey(Key_GUID(''SSN_Key_01''), @Phone));
		
				CLOSE SYMMETRIC KEY SSN_Key_01;
			END;'
	);

	EXEC
	(
		'CREATE PROCEDURE UpdatePhoneNumber
				@OldPhoneNumber varchar(12),
				@NewPhoneNumber varchar(12)
			AS
			BEGIN
				OPEN SYMMETRIC KEY SSN_Key_01 
				DECRYPTION BY CERTIFICATE PhoneCertificate;
			
				UPDATE [PhoneDb].[dbo].[Phones]
				SET   Phone 		   = @NewPhoneNumber,
					  EncryptedPhone   = EncryptByKey(Key_GUID(''SSN_Key_01''), @NewPhoneNumber)
				WHERE Phone = @OldPhoneNumber;
		
				CLOSE SYMMETRIC KEY SSN_Key_01;
			END;'
	);

	EXEC
	(
	'CREATE PROCEDURE DeletePhoneNumber
			@PlainPhoneNumber varchar(12)
		AS
		BEGIN
			DELETE FROM [PhoneDb].[dbo].[Phones]
			WHERE Phone = @PlainPhoneNumber;
		END;'
	);

	------------------------------
	-- USERS' STORED PROCEDURE --
	------------------------------
	EXEC
	(
		'CREATE PROCEDURE GetAllUsers
			AS
				SELECT * 
				FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
				ORDER BY Department, UserId;'
	);

	EXEC
	(
		'CREATE PROCEDURE GetUserByName
				@Name varchar(50)
			AS
			BEGIN
				SELECT * 
				FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
				WHERE Name LIKE ''%'' + @Name + ''%''
				ORDER BY Department, UserId;	
			END;'
	);

	EXEC
	(
		'CREATE PROCEDURE GetUserByDepartment
				@Department varchar(50)
			AS
			BEGIN
				SELECT * 
				FROM [PhoneDb].[dbo].[PhonesOwnedByUser]
				WHERE Department LIKE ''%'' + @Department + ''%''
				ORDER BY Department, UserId;	
			END;'
	);

	EXEC
	(
		'CREATE PROCEDURE InsertUser
				@Name 			  varchar(50),
				@Department 	  varchar(50),
				@PlainPhoneNumber varchar(12)
			AS
			BEGIN
				DECLARE @LastUserId int;
				DECLARE @IsPhoneExist int;

				IF @PlainPhoneNumber NOT LIKE ''0[3-4] [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]''
					RETURN
			
				SELECT @IsPhoneExist = COUNT(Phone) 
				FROM [PhoneDb].[dbo].[Phones] 
				WHERE Phone = @PlainPhoneNumber;
				
				IF @IsPhoneExist > 0
					RETURN
			
				INSERT INTO [PhoneDb].[dbo].[Users] (Name, DepartmentId)
					SELECT @Name, DepartmentId
					FROM [PhoneDb].[dbo].[Departments]
					WHERE Name = @Department;
		
				EXEC GetLastInsertId ''[PhoneDb].[dbo].[Users]'', @LastUserId OUTPUT
				EXEC InsertNewPhone @LastUserId, @PlainPhoneNumber;
			END;'
	);

	EXEC
	('CREATE PROCEDURE UpdateUser
			@UserId int,
			@Name	varchar(50)
		AS
		BEGIN
			UPDATE [PhoneDb].[dbo].[Users]
			SET   Name   = @Name
			WHERE UserId = @UserId;			
		END;'
	);

	EXEC
	(
		'CREATE PROCEDURE DeleteUser
				@UserId int
			AS
			BEGIN
				DELETE FROM [PhoneDb].[dbo].[Users]
				WHERE UserId = @UserId;
			END;'
	);

	----------------------------------------
	-- General PhoneDb's Stored Procedure --
	----------------------------------------
	EXEC
	(
		'CREATE PROCEDURE GetLastInsertId
				@TableName varchar(50),
				@LastId    int 			OUTPUT
			AS
			BEGIN
				SELECT @LastId = IDENT_CURRENT (@TableName);
			END;'
	);
END --IF DB_ID('PhoneDb') IS NOT NULL